package com.ukefu.webim.service.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.ukefu.webim.util.server.message.ChatMessage;
import com.ukefu.webim.web.model.AgentService;
import com.ukefu.webim.web.model.AgentStatus;
import com.ukefu.webim.web.model.AgentUserTask;

public abstract interface OnlineUserRepository extends JpaRepository<AgentService, String>{
//	public abstract OnlineUser findBySessionidAndOrgi(String paramString, String orgi);
//	
//	public abstract List<OnlineUser> findByUseridAndOrgi(String userid, String orgi);
//	
//	public abstract int countByUseridAndOrgi(String userid, String orgi);
//	
//	public abstract Page<OnlineUser> findByUseridAndOrgi(String userid, String orgi , Pageable page);
//	
//	public abstract OnlineUser findByOrgiAndSessionid(String orgi , String sessionid);
//	
//	public abstract Page<OnlineUser> findByOrgiAndStatusAndCreatetimeLessThan(String orgi , String status , Date createtime , Pageable paramPageable);
//
//	public abstract Page<OnlineUser> findByStatusAndCreatetimeLessThan(String status , Date createtime , Pageable paramPageable);
//	
//	public abstract Page<OnlineUser> findByOrgiAndStatus(String paramString1,String paramString2, Pageable paramPageable);
//	
//	@Query("select invitestatus , count(id) as users from OnlineUser where orgi = ?1 and status = ?2 group by invitestatus")
//	List<Object> findByOrgiAndStatus(String orgi ,String status);
	
	@Query("select result , count(id) as records from InviteRecord where orgi = ?1 and agentno = ?2 and createtime > ?3 and createtime < ?4 group by result")
	List<Object> findByOrgiAndAgentnoAndCreatetimeRange(String orgi ,String agentno , Date start , Date end);
	
	@Query("select result , count(id) as records from InviteRecord where orgi = ?1 and agentno = ?2 group by result")
	List<Object> findByOrgiAndUserid(String orgi ,String userid);

	@Query("select count(id) from AgentService where orgi = ?1 and status = ?2 and agentno = ?3 and createtime > ?4 and createtime < ?5")
	Long countByAgentForAgentUser(String orgi ,String status,String agentno , Date start , Date end);
	
	@Query("select count(id) from AgentService where orgi = ?1 and agentno = ?2 and createtime > ?3 and createtime < ?4")
	Long countByAgentForService(String orgi ,String agentno , Date start , Date end);

	@Query("select count(id) from AgentService where orgi = ?1 and status = ?2 and agentno = ?3 and createtime > ?4 and createtime < ?5")
	Long countByAgentForAgentService(String orgi ,String status,String agentno , Date start , Date end);
	
	@Query("select avg(sessiontimes) from AgentService where orgi = ?1 and status = ?2 and agentno = ?3 and createtime > ?4 and createtime < ?5")
	Long countByAgentForAvagTime(String orgi ,String status,String agentno , Date start , Date end);
	
	
	@Query("select avg(sessiontimes) from AgentService where orgi = ?1 and status = ?2 and userid = ?3")
	Long countByUserForAvagTime(String orgi ,String status,String userid);
	
//	@Query("select createdate as dt, count(distinct ip) as ips ,  count(id) as records from UserHistory where orgi = ?1 and model = ?2 and createtime > ?3 and createtime < ?4 group by createdate order by dt asc")
//	List<Object> findByOrgiAndCreatetimeRange(String orgi , String model ,Date start , Date end);
	
	@Query("select createdate as dt, count(id) as users from AgentService where orgi = ?1 and createtime > ?2 and createtime < ?3 group by createdate order by dt asc")
	List<Object> findByOrgiAndCreatetimeRangeForAgent(String orgi , Date start , Date end);
	
	@Query("select osname, count(id) as users from AgentService where orgi = ?1 and createtime > ?2 and createtime < ?3 and channel = ?4 group by osname")
	List<Object> findByOrgiAndCreatetimeRangeForClient(String orgi , Date start , Date end , String channel);
	
	@Query("select browser, count(id) as users from AgentService where orgi = ?1 and createtime > ?2 and createtime < ?3 and channel = ?4 group by browser")
	List<Object> findByOrgiAndCreatetimeRangeForBrowser(String orgi , Date start , Date end , String channel);
	
	@Query("select agentno, count(id) as users from AgentService where orgi = ?1 and userid = ?2 group by agentno")
	List<Object> findByOrgiForDistinctAgent(String orgi , String userid);
	
	@Query("select count(id) from AgentService where orgi = ?1 and appid = ?2 and createtime > ?3 and createtime < ?4")
	Long countByOrgiAndAppidForCount(String orgi ,String appid ,Date start,Date end);
	
	@Query("select count(id) from StatusEvent where discaller = ?1 and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long countByCallerFromCallCenter(String caller,String orgi);
	
	@Query("select count(id) from StatusEvent where discalled = ?1 and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long countByCalledFromCallCenter(String called ,String orgi);
	
	@Query("select count(id) from StatusEvent where (discaller = ?1 or discalled = ?1) and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long countByAniFromCallCenter(String ani,String orgi);
	
	//统计坐席的全部通话数，查询通话记录是坐席的ID的通话记录 --- 当天
	@Query("select count(id) from StatusEvent where userid = ?1 and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long countByUseridFromCallCenter(String userid,String orgi);
	
	//统计坐席的呼出通话数，查询主叫是坐席的分机号的通话记录 --- 当天
	@Query("select count(id) from StatusEvent where userid = ?1 and orgi = ?2 and discaller = ?3 and to_days(createtime) = to_days(now())")
	Long countByUseridAndOutcallsFromCallCenter(String userid,String orgi ,String discaller);
	
	//统计坐席的呼入（接电话）通话数，查询被叫是坐席的分机号的通话记录 --- 当天
	@Query("select count(id) from StatusEvent where userid = ?1 and orgi = ?2 and discalled = ?3 and to_days(createtime) = to_days(now())")
	Long countByUseridAndIncallsFromCallCenter(String userid,String orgi ,String discalled);
	
	//振铃 只计算未接通电话
	@Query("select avg(ringduration) from StatusEvent where ( discaller = ?1 or discalled = ?1 ) and misscall = true  and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long avgByRingDurationFromCallCenter(String ani,String orgi);
	
	@Query("select avg(ringduration) from StatusEvent where userid = ?1 and misscall = true  and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long avgByRingDurationAndUseridFromCallCenter(String userid,String orgi);
	
	//通话 只计算接通电话
	@Query("select avg(duration) from StatusEvent where ( discaller = ?1 or discalled = ?1 )  and misscall = false and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long avgByDurationFromCallCenter(String ani,String orgi);
	
	@Query("select avg(duration) from StatusEvent where userid = ?1  and misscall = false and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long avgByDurationAndUseridFromCallCenter(String userid,String orgi);
	
	
	@Query("select sum(ringduration) from StatusEvent where ( discaller = ?1 or discalled = ?1 )  and misscall = true and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long sumByRingDurationFromCallCenter(String ani,String orgi);
	
	@Query("select sum(ringduration) from StatusEvent where userid = ?1  and misscall = true and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long sumByRingDurationAndUseridFromCallCenter(String userid,String orgi);
	
	@Query("select avg(ringduration) from StatusEvent where userid = ?1  and misscall = false and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long avgByRingdurationAndUseridFromCallCenter(String userid,String orgi);
	
	@Query("select sum(duration) from StatusEvent where ( discaller = ?1 or discalled = ?1 )  and misscall = false and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long sumByDurationFromCallCenter(String ani,String orgi);
	
	@Query("select sum(duration) from StatusEvent where userid = ?1  and misscall = false and orgi = ?2 and to_days(createtime) = to_days(now())")
	Long sumByDurationAndUseridFromCallCenter(String userid,String orgi);
	
	
	@Query("select hourstr as dt, count(id) as calls from StatusEvent where orgi = ?1 and datestr = ?2 group by hourstr order by dt asc")
	List<Object> findByOrgiAndDatestrRangeForAgent(String orgi , String start );
	
	@Query("select code as dt, count(id) as co from CallMonitor where orgi = ?1 group by code")
	List<Object> findByOrgiAndStatusRangeForAgent(String orgi);
	
	@Query("select s from StatusEvent s  where startrecord<= ?1 AND ORGI = ?2 AND (discalled = ?3 OR discaller= ?4 )")
	List<Object> findByOrgiAndStartrecord(Date startrecord, String orgi, String discalled, String discaller);
	
	@Query("delete from CallOutNames where actid = ?2 AND ORGI = ?1")
	void deleteByOrgiAndActid(String orgi, String actid);
	
	@Query("SELECT e from EkmExperts e WHERE orgi = ?1 AND (bustype = ?2 or bustype = ?3 )")
	Page<Object> findByExperts(String orgi, String exp, String au, Pageable paramPageable);
	
	@Query("select e from EkmKwSearch e where orgi = ?1 and badword = 0 and type = ?2  group by conditions order by count(id) desc")
	List<Object> findByOrgiAndSearch(String orgi,String type);
	
	@Query("select e from EkmKwSearchTag e where orgi = ?1  group by tag order by count(id) desc")
	List<Object> findByOrgiAndTag(String orgi);
	
	//查询当天通话总数
	@Query("select count(id) from StatusEvent where orgi = ?1 and ?2 < createtime and createtime < ?3 and (CALLTYPE= 'insideline' or CALLTYPE= 'outsideline' or CALLTYPE = 'orgcallout')")
	Long countByToadyCalledFromStatusEvent(String orgi, Date begin, Date end);
	
	//查询当天是否漏话总数
	@Query("select count(DISTINCT membersessionid) from StatusEvent where orgi = ?1 and misscall = ?2 and ?3 < createtime and createtime < ?4 and quene is not null")
	Long countByToadyMissCalledFromStatusEvent(String orgi, boolean misscall, Date begin, Date end);

	//查询当天呼入通话总数
	@Query("select count(id) from StatusEvent where orgi = ?1 and ?2 < createtime and createtime < ?3 and CALLTYPE= 'outsideline'")
	Long countByToadyCallInFromStatusEvent(String orgi, Date begin, Date end);
	
	//查询当天呼入与呼出-通话总数
	@Query("select count(id) from StatusEvent where orgi = ?1 and calltype = ?2 and ?3 < createtime and createtime < ?4")
	Long countByToadyDirectionFromStatusEvent(String orgi, String calltype, Date begin, Date end);
	
	//查询当天指定分机的 呼出-通话总数
	@Query("select count(id) from StatusEvent where orgi = ?1 and calltype = ?2 and discaller = ?3 and ?4 < createtime and createtime < ?5")
	Long countByToadyAniFromStatusEvent(String orgi, String calltype, String ani, Date begin, Date end);

	//查询指定分机当天-通话总数
	@Query("select count(id) from StatusEvent where orgi = ?1 and (discaller = ?2 or discalled = ?3) and ?4 < createtime and createtime < ?5")
	Long countByToadyExtentionFromStatusEvent(String orgi, String ani, String called, Date begin, Date end);
	
	//查询指定分机当天-平均通话时长 （接通）
	@Query("select AVG(duration) from StatusEvent where orgi = ?1 and (discaller = ?2 or discalled = ?3) and ?4 < createtime and createtime < ?5 and misscall = false ")
	Long countByToadyExtDurFromStatusEvent(String orgi, String ani, String called, Date begin, Date end);
	
	//查询指定分机当天-平均振铃时长 (未接通)
	@Query("select AVG(ringduration) from StatusEvent where orgi = ?1 and (discaller = ?2 or discalled = ?3) and ?4 < createtime and createtime < ?5 and misscall = true ")
	Long countByToadyExtRingFromStatusEvent(String orgi, String ani, String called, Date begin, Date end);
	
	//根据质检人和质检状态
	@Query("select count(id) from QualityMissionHis where orgi = ?1 and qualityuser = ?2 and qualitystatus = ?3")
	Long countByQualityuserAndQualitystatusFromQualityMissionHis(String orgi, String qualityuser, String qualitystatus);
	
	@Query("select count(id) from QualityMissionHis where orgi = ?1 and qualityuser = ?2")
	Long countByQualityuserFromQualityMissionHis(String orgi, String qualityuser);
	
	//根据分配质检人和质检状态
	@Query("select count(id) from QualityMissionHis where orgi = ?1 and qualitydisuser = ?2 and qualitystatus = ?3")
	Long countByQualitydisuserAndQualitystatusFromQualityMissionHis(String orgi, String qualitydisuser, String qualitystatus);
	
	@Query("select count(id) from QualityMissionHis where orgi = ?1 and qualitydisuser = ?2")
	Long countByQualitydisuserFromQualityMissionHis(String orgi, String qualitydisuser);
	
	//根据质检人和质检是否合格
	@Query("select count(id) from QualityMissionHis where orgi = ?1 and qualityuser = ?2 and qualitypass = ?3")
	Long countByQualityuserAndQualitypassFromQualityMissionHis(String orgi, String qualityuser,int qualitypass);
	
	//根据质检人 、质检是否申诉、 质检是否仲裁
	@Query("select count(id) from QualityMissionHis where orgi = ?1 and qualityuser = ?2 and qualityappeal = ?3 and qualityarbitrate = ?4")
	Long countByQualityuserAndQualityappealAndQualityarbitrateFromQualityMissionHis(String orgi, String qualityuser,int qualityappeal,int qualityarbitrate);
	
	@Query("select qualityorgan,count(id) as misscount from QualityMissionHis  where orgi = ?1  GROUP BY qualityorgan ")
	List<Object> findGroupbyOrganFromQualityMissionHis(String orgi);
	
	@Query("select qualityorgan,count(id) as passcount  from QualityMissionHis  where orgi = ?1 and qualitypass = ?2 GROUP BY qualityorgan ")
	List<Object> findByQualitypassGroupbyOrganFromQualityMissionHis(String orgi, int qualitypass);
	
	@Query("select qualityorgan,count(id) as passcount  from QualityMissionHis  where orgi = ?1 and qualitypass = ?2 and qualitytype=?3 GROUP BY qualityorgan ")
	List<Object> findByQualitypassGroupbyOrganFromQualityMissionHis(String orgi, int qualitypass,String qualitytype);
	
	//计算每小组平均质检效率
	@Query("select qualityorgan,AVG(date_format(timediff(qualitytime,createtime),'%s')+date_format(timediff(qualitytime,createtime),'%i')*60) as efficiency  from QualityMissionHis  where orgi = ?1  GROUP BY qualityorgan")
	List<Object> avgByQuanlitytimebyOrganFromQualityMissionHis(String orgi);
	
	
	@Query("select qualityorgan,count(id) as misscount from QualityMissionHis  where orgi = ?1 and qualitystatus=?2  GROUP BY qualityorgan ")
	List<Object> findByQualitystatusGroupbyOrganFromQualityMissionHis(String orgi,String qualitystatus);
	
	@Query("select qualityorgan,count(id) as misscount from QualityMissionHis  where orgi = ?1 and qualityappeal=?2  GROUP BY qualityorgan ")
	List<Object> findByQualityappealGroupbyOrganFromQualityMissionHis(String orgi,int qualityappeal);
	
	@Query("select qualityorgan,count(id) as misscount from QualityMissionHis  where orgi = ?1 and qualityarbitrate=?2  GROUP BY qualityorgan ")
	List<Object> findByQualityarbitrateGroupbyOrganFromQualityMissionHis(String orgi,int qualityarbitrate);
	
	@Query("select count(id) as allcount,"
			+ "count(case when qualitystatus = 'done' THEN '1' end) as donecount,"
			+ "count(case when qualitystatus = 'recheck' THEN '2' end) as recount,"
			+ "count(case when qualitystatus = 'no' THEN '3' end) as nocount,"
			+ "count(case when qualitystatus = 'appeal' THEN '4' end) as appealcount,"
			+ "count(case when qualitystatus = 'abritrate' THEN '5' end) as myabritratecount,"
			+ "count(case when qualityuser = ?1 and qualitypass = 0 THEN '6' end) as nopasscount,"
			+ "count(case when qualityuser = ?1 and qualityappeal = 1  THEN '7' end) as myappealcount,"
			+ "count(case when qualityuser = ?1 and qualityappeal = 1 and qualityarbitrate = 1 THEN '8' end) as myarbitratecount "
			+ "FROM QualityMissionHis "
			+ "where qualitydisuser = ?1 and orgi = ?2")
	Map<String,Long> countQc(String qualityuser,String orgi);
	
	
	@Query("select "
			+ "count(case when qualitystatus = 'appeal' THEN '1' end) as appealcount ,"
			+ "count(case when qualitystatus = 'arbitrate' THEN '2' end) as arbitratecount "
			+ "FROM QualityMissionHis "
			+ "where userid = ?1 and orgi = ?2")
	Map<String,Long> countQcOwn(String qualityuser,String orgi);
	
	@Query("select qualityorgan as organid,"
			+"count(id) as misscount,"
			+"count(case when qualitypass = 1 and qualitytype = 'callevent' THEN '1' end) as organpasscallcount,"
			+"count(case when qualitypass = 1 and qualitytype = 'agentservice' THEN '2' end) as organpassagentcount,"
			+"count(case when qualitypass = 1 and qualitytype = 'workorders' THEN '3' end) as organpassworkcount,"
			+"count(case when qualitystatus = 'appeal' THEN '4' end) as disablecount,"
			+"count(case when qualitypass = 0 THEN '5' end) as organnopasscount,"
			+"count(case when qualityappeal = 0 THEN '6' end) as organappealcount,"
			+"count(case when qualityarbitrate = 0 THEN '7' end) as organarbitratecount,"
			+"AVG((qualitytime-createtime)/1000) as efficiency, "
			+"count(case when qualitytype = 'callevent' THEN '8' end) as organcallcount,"
			+"count(case when qualitytype = 'agentservice' THEN '9' end) as organagentcount,"
			+"count(case when qualitytype = 'workorders' THEN '10' end) as organworkcount "
		    +"FROM QualityMissionHis "
		    +"where orgi = ?1 GROUP BY qualityorgan")
	List<Map<String,Long>> countQcOrgan(String orgi);
	
	@Query("select count(id) as allcount,"
			//底下
			+"count(case when status = 'inservice' THEN '1' end) as inservicecount,"//咨询中的会话
			+"count(case when status = 'inquene' THEN '2' end) as inquenecount,"//等待接入的会话
			+"count(case when agentfrewords > 0 THEN '3' end) as frewordscount,"//坐席敏感词报警的会话
			+"count(case when sessiontimeout > 0 THEN '4' end) as sessiontimeoutcount,"//会话超时的会话
			//上方
			+"sum(agentfrewords) as agentfrewordssum,"//坐席敏感词报警
			+"sum(servicefrewords) as servicefrewordssum,"//访客敏感词报警
			+"sum(msgtimeoutagent) as msgtimeoutagentsum,"//坐席会话超时次数
			+"sum(sessiontimeout) as sessiontimeoutsum,"//会话超时次数
			+"count(case when status ='end' and servicetime is null and queuetime is not null  THEN '5' end) as quenetimeoutcount ,"//排队超时次数
			+"count(case when status ='end' and userasks = 0 and servicetime is not null THEN '6' end) as invalidcount ,"//无效会话次数
			+"count(case when status ='end' and servicetime is null and queuetime is not null and endby='user'  THEN '7' end) as queneoutcount ,"//访客排队中离开
			+"count(case when status ='end' and servicetime is not null and userasks = 0  THEN '8' end) as agentoutcount ,"//进入会话后离开
			+"sum(satisfactionalarms) as satisfactionalarmscount ,"//满意度报警次数
			+"sum(invitevals) as invitevalscount ,"//邀请评价次数
			+"sum(resptimeouts) as resptimeoutscount "//响应超时次数
		    +"FROM AgentUserTask "
		    +"where orgi = ?1 ")
	Map<String,Long> countAgentUserTask(String orgi);
	
	//平均响应超时时长
	@Query("select avg(avgreplytime) from AgentUserTask where avgreplytime > 0 and orgi = ?1")
	Long avgByAvgreplytimeFromAgentUserTask(String orgi);
	
	//平均超时时长
	@Query("select avg(agentservicetimeout) from AgentUserTask where agentservicetimeout != 0 and orgi = ?1")
	Long avgByAgentservicetimeoutFromAgentUserTask(String orgi);
	
	//平均排队时长
	 @Query("select AVG((case when servicetime is null then unix_timestamp(now()) else unix_timestamp(servicetime) end) - unix_timestamp(queuetime)) from AgentUserTask where queuetime != '' and orgi = ?1 and ((queuetime !='' and queuetime is NOT NULL and servicetime is NOT NULL and queuetime<servicetime) or (servicetime is NULL and status = 'inquene' and queuetime !=''))")
	 Long avgByQueueavgFromAgentUserTask(String orgi);
	
	//排队超时次数、
	@Query("select count(id) from AgentUserTask where (unix_timestamp(now())-unix_timestamp(queuetime) > ?1 and status='inquene' and (servicetime = null or queuetime > servicetime))or(queuetime < servicetime and unix_timestamp(servicetime)-unix_timestamp(queuetime) > ?1) and orgi = ?2")
	Long countByInquenetimeoutFromAgentUserTask(long timeout,String orgi);
	
	//排队超时
	@Query("select agent from AgentUserTask agent where (unix_timestamp(now())-unix_timestamp(queuetime) > ?1 and status='inquene' and (servicetime = null or queuetime > servicetime))or(queuetime < servicetime and unix_timestamp(servicetime)-unix_timestamp(queuetime) > ?1) and orgi = ?2 ORDER BY createtime DESC")
	Page<AgentUserTask> findByInquenetimeoutFromAgentUserTask(long timeout,String orgi, Pageable page);
	
	
	@Query(nativeQuery=true, value = "select uk_agentstatus.agentno , count(uk_agentuser.userid) as users from uk_agentstatus left join uk_agentuser on uk_agentstatus.agentno = uk_agentuser.agentno where uk_agentstatus.orgi = ?1 AND uk_agentstatus.busy = 0 AND ((uk_agentstatus.maxusers > 0 AND users < uk_agentstatus.maxusers) OR (uk_agentstatus.maxusers = 0 AND users < ?2)) group by uk_agentstatus.agentno order by ?3 asc limit 0 , 2")
	List<Object> findAgentByOrgi(String orgi , int maxuser , String field);
	
	
	@Query(nativeQuery=true, value = "select uk_agentstatus.agentno , count(uk_agentuser.userid) as users from uk_agentstatus left join uk_agentuser on uk_agentstatus.agentno = uk_agentuser.agentno where uk_agentstatus.agentno = ?1 AND uk_agentstatus.busy = 0 AND uk_agentstatus.orgi = ?2 AND ((uk_agentstatus.maxusers > 0 AND users < uk_agentstatus.maxusers) OR (uk_agentstatus.maxusers = 0 AND users < ?3)) group by uk_agentstatus.agentno order by ?4 asc limit 0 , 2")
	List<Object> findAgentByAgentnoAndOrgi(String agentno,String orgi , int maxuser, String field);
	
	@Query(nativeQuery=true, value = "select uk_agentstatus.agentno , count(uk_agentuser.userid) as users from uk_agentstatus left join uk_agentuser on uk_agentstatus.agentno = uk_agentuser.agentno where uk_agentstatus.skill = ?1 AND uk_agentstatus.busy = 0 AND uk_agentstatus.orgi = ?2 AND ((uk_agentstatus.maxusers > 0 AND users < uk_agentstatus.maxusers) OR (uk_agentstatus.maxusers = 0 AND users < ?3)) group by uk_agentstatus.agentno order by ?4 asc limit 0 , 2")
	List<Object> findAgentBySkillAndOrgi(String agentno,String orgi , int maxuser, String field);
	
	@Query("select agent from AgentStatus agent where busy = 0 AND orgi = ?1 AND ((maxusers > 0 AND users < maxusers) OR (maxusers = 0 AND users < ?2)) ORDER BY users ASC")
	Page<AgentStatus> findByAgentAndOrgi(String orgi , int maxuser , Pageable page);
	
	@Query("select count(userid) from AgentUser where orgi = ?1 and agentno = ?2")
	Long countByAgentno(String orgi, String agentno);
	
	
	@Query("select count(id) as allcount,"
			+"count(case when triggertime is not null THEN '1' end) as triggertimecount,"//触发预警
			+"count(case when triggerwarning is not null THEN '2' end) as triggerwarningcount,"//拦截次数
			+"count(case when triggertime is not null and userid is not null THEN '3' end) as usertriggercount,"//触发预警用户
			+"count(case when triggerwarning is not null and userid is not null THEN '4' end) as userwarningcount,"//被拦截的用户
			+"count(case when error is not null THEN '5' end) as errorcount,"//报错提示
			+"count(case when userid is null THEN '6' end) as notusercount "//非用户操作
		    +"FROM RequestLog "
		    +"where createdate >=?1 and createdate <=?2")
	Map<String,Long> countRequestLog( Date begin, Date end);
	
	@Query("select avg(querytime) from RequestLog where createdate >=?1 and createdate <=?2")
	Double avgByQuerytimeFromRequestLog( Date begin, Date end);
	
	@Query("select ip FROM RequestLog where createdate >=?1 and createdate <=?2 and ip is not null GROUP BY ip")
	List<Object> ipGroupbyFromRequestLog( Date begin, Date end);
	
	@Query("select userid FROM RequestLog where createdate >=?1 and createdate <=?2 and userid is not null GROUP BY userid")
	List<Object> useridGroupbyFromRequestLog( Date begin, Date end);
	
	@Query("select count(id) as alluser,"
			+"count(case when usertype = '0' THEN '1' end) as admins,"//管理员
			+"count(case when agent = ?1 THEN '2' end) as agents "//坐席数
		    +"FROM User "
		    +"where datastatus = ?2")
	Map<String,Long> countUser( boolean agent,boolean datastatus);
	
	@Query("select avg(sessiontimes) from AgentService where sessiontimes is not null")
	Long avgBySessiontimesFromAgentService();
	
	@Query(value = "select msg from ChatMessage msg where ((userid = ?1 AND contextid = ?2) OR (userid = ?2 and contextid = ?1)) and orgi = ?3  order by createtime desc ")
	Page<ChatMessage> findByContextidAndOrgi(String userid,String contextid, String orgi,Pageable pageable);
	
	@Query(value="select username,"//0
			+ "count(1) ,"//1通话总量
			+"ROUND(avg(recordtime/1000),2), "//2人工坐席平均通话时长----单位：秒 已更改为：单通平均通话时长
			+ "sum(duration),"//3人工坐席通话总时长---单位：毫秒
			+ "ROUND(avg(ringduration/1000),2), "//4振铃平均时长 ---单位：秒
			+ "sum(asrtimes),"//5调用asr总次数
			+ "ROUND(avg(asrtimes),2),"//6调用asr平均次数
			+"count(case when ai=false and srecord=true and con_quality='1' and con_invitation = '2c92a1e370ad651e0170adb359070354' THEN '1' end) as qualitytotal,"//7质检总量
			+"count(case when ai=false and srecord=true and con_qualitypass='1' and con_invitation = '2c92a1e370ad651e0170adb359070354' THEN '2' end) as qualitysuccess,"//8质检成功量
			+"count(case when ai=false and srecord=true and con_quality='1' and (con_qualitypass is null or con_qualitypass!='1') and con_invitation = '2c92a1e370ad651e0170adb359070354' THEN '3' end) as qualityfail,"//9质检失败量
			+"count(case when ai=false and srecord=true and (con_quality is null or con_quality='0') and con_invitation = '2c92a1e370ad651e0170adb359070354' THEN '4' end) as qualityno,"//10未质检量
			+"count(case when ai=false and srecord=true and con_invitation = '2c92a1e370ad651e0170adb359070354' THEN '5' end) as qualityshold, "//11应质检量
			+"ROUND(avg(duration/1000),2), "//单位：秒  人工坐席通话平均时长
			+"count(case when ai=false and con_invitation = '2c92a1e370ad651e0170adb359070354' and con_qualitysubmit = '1' THEN '6' end) as invitation, "//成功件
			+"count(case when ai=false and con_invitation = '2c92a1e370ad651e0170adb359070354' THEN '7' end) as invitationtotal "//总邀约量
		    +"FROM uk_callcenter_event "
		    +"where createtime >=?1 and createtime <=?2 and orgi=?3 and username is not null and aitrans = true AND IF(?4!='',actid = ?4,1=1) GROUP BY username",nativeQuery = true)
	List<Object> aggrAgentGroupbyUsername( Date begin, Date end,String orgi,String actid);
	
	@Query(value="select agent,"//0
			+ "count(*) ,"//1机器人外呼总量
			+"count(case when aitrans = 1 and ai=1 and duration > 0 THEN '1' end) as transtotal,"//2转人工总量
			+ "ROUND(avg(aitransduration),2),"//3转人工平均时长
			+ "ROUND(avg(case when ai=1 and duration > 0 THEN duration/1000 end),2),"//4机器人平均通话时长
			+ "ROUND(avg(ringduration/1000),2), "//5振铃平均时长
			+ "sum(duration),"//6机器人通话总时长
			+ "sum(asrtimes),"//7调用asr总次数
			+ "ROUND(avg(case when ai=1 and duration > 0 THEN asrtimes end),2), "//8调用asr平均次数
			+ "count(case when ai=1 and duration > 0 THEN '2' end) as aicutin "//9接通量
		    +"FROM uk_callcenter_event "
		    +"where createtime >=?1 and createtime <=?2 and orgi=?3 and dataid is not null and username is null and agent is not null and !(ai=0 and aitrans=1) AND IF (?4!='',actid = ?4,1=1) GROUP BY agent",nativeQuery = true)
	List<Object> aggrAgentGroupbyAiAgent( Date begin, Date end,String orgi,String actid);
	
	@Query("select count(case when dataid is not null and username is null and ((ai=true and aitrans=true)or(ai=true and aitrans=false)or(ai=false and aitrans=false)) THEN '1' end) as calltotal,"//0通话总量
			+"count(case when ai=true and username is null and agent is not null and duration > 0 THEN '2' end) as answertotal,"//1接通总量
			+"count(case when aitrans = true and ai = false and username is not null THEN '3' end) as aitranstotal,"//3转人工总量
			+"count(case when con_invitation = '2c92a1e370ad651e0170adb359070354' and con_qualitysubmit = '1' and ai=false and aitrans=true THEN '4' end) as invitationsuccess,"//6成功邀约
			+"count(case when ai =true and transfaild = true THEN '5' end) as transfailds, "//7转人工失败量
			+"count(case when username is null and agent is not null and ai=true and aitrans=true and duration > 0 THEN '6' end) as cantrans, "//8可转人工
			+"count(case when ai = false and aitrans = true and transfaild = true THEN '7' end) as transfakefailds, "//9假损量
			+"ROUND(SUM(case when ((ai=true and aitrans=true) or (ai=true and aitrans=false)or(ai=false and aitrans=false)) THEN duration end)/1000) as sumAidurationMonitor, "//10机器人通话总时长
			+"ROUND(sum(case when ai=false and aitrans=true THEN duration end)/1000) as sumAgentdurationMonitor, "//11转人工通话总时长
			+"ROUND(sum(case when ai = false and aitrans = true and waittime > 0 THEN waittime end)/1000) as sumWaittimeMonitor, "//12总等待时长
			+"ROUND(avg(case when ((ai=true and aitrans=true)or(ai=true and aitrans=false)or(ai=false and aitrans=false)) and duration!='0' THEN duration end)/1000) as avgAidurationMonitor ,"//14机器人平均通话时长
			+"count(DISTINCT case when username is not null and ai=false and aitrans=true THEN username end) as countAgentMonitor, "//15坐席数量
			+"ROUND(sum(case when ai= true THEN ringduration end)/1000) as sumringduration,"//16机器人振铃总时长
			+"ROUND(avg(case when ai= true THEN ringduration end)/1000) as avgringduration, "//17机器人振铃平均时长
			+"count(case when con_invitation = '2c92a1e370ad651e0170adb359070354' and ai=false and aitrans=true THEN '8' end) as invitations,"//18邀约总数
			+"count(case when con_invitation = '2c92a1e370ad651e0170adb359070354' and con_qualitysubmit = '0' and ai=0 and aitrans=1 THEN '9' end) as qualitysubmitno, "//19质检失败量
			+"count(case when aicollect=1 and ai=1 and aitrans=1 THEN '10' end) as aicollectsum, "//20机器人质检总量
			+"count(case when aicollect=1 and ai=1 and aitrans=1 and con_qualitypass='1' THEN '11' end) as aicollectqualitysuccess "//21机器人质检成功量
		    +"FROM StatusEvent "
		    +"where createtime >=?1 and createtime <=?2 and orgi=?3")
	Map<String,Object> aggrCallcenterMonitor( Date begin, Date end,String orgi);
	
	@Query("select count(case when dataid is not null and username is null and ((ai=true and aitrans=true)or(ai=true and aitrans=false)or(ai=false and aitrans=false)) THEN '1' end) as calltotal,"//0通话总量
			+"count(case when ai=true and username is null and agent is not null and duration > 0 THEN '2' end) as answertotal,"//1接通总量
			+"count(case when aitrans = true and ai = false and username is not null THEN '3' end) as aitranstotal,"//3转人工总量
			+"count(case when con_invitation = '2c92a1e370ad651e0170adb359070354' and con_qualitysubmit = '1' and ai=false and aitrans=true THEN '4' end) as invitationsuccess,"//6成功邀约
			+"count(case when ai =true and transfaild = true THEN '5' end) as transfailds, "//7转人工失败量
			+"count(case when username is null and agent is not null and ai=true and aitrans=true and duration > 0 THEN '6' end) as cantrans, "//8可转人工
			+"count(case when ai = false and aitrans = true and transfaild = true THEN '7' end) as transfakefailds, "//9假损量
			+"ROUND(SUM(case when ((ai=true and aitrans=true) or (ai=true and aitrans=false)or(ai=false and aitrans=false)) THEN duration end)/1000) as sumAidurationMonitor, "//10机器人通话总时长
			+"ROUND(sum(case when ai=false and aitrans=true THEN duration end)/1000) as sumAgentdurationMonitor, "//11转人工通话总时长
			+"ROUND(sum(case when ai = false and aitrans = true and waittime > 0 THEN waittime end)/1000) as sumWaittimeMonitor, "//12总等待时长
			+"ROUND(avg(case when ((ai=true and aitrans=true)or(ai=true and aitrans=false)or(ai=false and aitrans=false)) and duration!='0' THEN duration end)/1000) as avgAidurationMonitor ,"//14机器人平均通话时长
			+"count(DISTINCT case when username is not null and ai=false and aitrans=true THEN username end) as countAgentMonitor, "//15坐席数量
			+"ROUND(sum(case when ai= true THEN ringduration end)/1000) as sumringduration,"//16机器人振铃总时长
			+"ROUND(avg(case when ai= true THEN ringduration end)/1000) as avgringduration, "//17机器人振铃平均时长
			+"count(case when con_invitation = '2c92a1e370ad651e0170adb359070354' and ai=false and aitrans=true THEN '8' end) as invitations,"//18邀约总数
			+"count(case when con_invitation = '2c92a1e370ad651e0170adb359070354' and con_qualitysubmit = '0' and ai=0 and aitrans=1 THEN '9' end) as qualitysubmitno, "//19质检失败量
			+"count(case when aicollect=1 and ai=1 and aitrans=1 THEN '10' end) as aicollectsum, "//20机器人质检总量
			+"count(case when aicollect=1 and ai=1 and aitrans=1 and con_qualitypass='1' THEN '11' end) as aicollectqualitysuccess "//21机器人质检成功量
		    +"FROM StatusEvent "
		    +"where createtime >=?1 and createtime <=?2 and orgi=?3 and actid=?4")
	Map<String,Object> aggrCallcenterMonitor( Date begin, Date end,String orgi,String actid);
	
	@Query(value="select ROUND(sum(RINGDURATION)/1000) as sumringduration,"//机器人振铃总时长
			+ " ROUND(avg(RINGDURATION)/1000) as avgringduration "//机器人振铃平均时长
		    +"FROM uk_callcenter_event "
		    +"where ai=1 and  createtime >=?1 and createtime <=?2 and orgi=?3 AND IF (?4!='',actid = ?4,1=1)",nativeQuery = true)
	List<Object> aggrAiRingdurationMonitor( Date begin, Date end,String orgi,String actid);
	
	
	@Query("select "//
			+"count(case when createtime <=?3 and createtime >=?4 THEN '1' end) as servicetoy ,"//1今天接待量
			+"count(case when createtime <=?4 and createtime >=?5 THEN '2' end) as serviceyet ,"//2昨天接待量
			+"count(case when createtime <=?3 and createtime >=?6 THEN '3' end) as serviceweek ,"//3一周接待量
			+"count(case when createtime <=?3 and createtime >=?7 THEN '4' end) as servicemon "//4一月接待量
		    +"FROM AgentService "
		    +"where orgi=?1 and agentno=?2")
	Map<String,Long> aggrAgentServiceByAgentno(String orgi,String agentno,Date tomorrowdate,Date today,Date yesterday,Date weekdate,Date monthdate);
	
	@Query("select count(*) as agentservices,"
			+"count(case when status = 'inquene' THEN '1' end) as inquenecount "//排队量
		    +"FROM AgentUser "
		    +"where orgi = ?1 and agentno = ?2")
	Map<String,Long> aggrAgentUserByAgentno(String orgi,String agentno);
	
	@Query("select count(case when useful = false THEN '1' end) as useful,"//解决
			+"count(case when useful = true THEN '2' end) as notuseful "//
		    +"FROM AgentService "
		    +"where orgi = ?1 and agentno = ?2 and status ='end' and userasks > 0  and createtime >=?3 and createtime <=?4")
	Map<String,Long> aggrAgentUserUserfulByAgentno(String orgi,String agentno ,Date begin, Date end);
	
	@Query("select count(case when useful = false THEN '1' end) as useful,"//解决
			+"count(case when useful = true THEN '2' end) as notuseful "//
		    +"FROM AgentService "
		    +"where orgi = ?1 and agentno = ?2 and status ='end' and userasks > 0")
	Map<String,Long> aggrAgentUserUserfulByAgentno(String orgi,String agentno );
	
	@Query("select satislevel,count(*) as quantity "//满意度
		    +"FROM AgentService "
		    +"where orgi = ?1 and agentno = ?2 and satislevel is not null and createtime >=?3 and createtime <=?4 group by satislevel")
	List<Object> aggrAgentUserSatislevelByAgentno(String orgi,String agentno,Date begin, Date end );
	
	@Query("select se.id,se.ani,se.direction,se.duration,se.province,se.city,se.createtime,se.orgi from StatusEvent se where orgi=?1 and ani like ?2 and userid=?3 ORDER BY createtime DESC")
	Page<Object> searchStatusEventByQAndOrgi(String orgi , String q,String agentno , Pageable page);
	
	
	@Query(value="select count(*) as total,"//1总
			+ "count(case when userid is not null THEN '2' end) as disedtotal,"//2已分配数量
			+"count(case when userid is not null and con_quality='1'  THEN '3' end) as qualitytotal,"//3质检数量
			+"count(case when userid is not null and con_quality='1' and con_qualitypass='1' THEN '4' end) as qualitysuccess, "//4质检通过数量
			+"count(case when userid is not null and con_quality='1' and (con_qualitypass is null or con_qualitypass!='1') THEN '5' end) as qualityfaild, "//5质检不通过数量
			+"count(case when userid is not null and con_qualitysubmit='1'THEN '6' end) as success, "//6成功件
			+"count(case when userid is not null and (con_qualitysubmit is null or con_qualitysubmit!='1')THEN '7' end) as faild, "//7失败件
			+"count(case when userid is null and (con_quality is null or con_quality!='1') THEN '3' end) as notquality "//8未质检
		    +"FROM uk_callcenter_event "
		    +"where ai=1 and aicollect=1 and servicestatus='hangup' AND IF (?1!='',createtime >= ?1,1=1) AND IF (?2!='',createtime <= ?2,1=1) and orgi=?3 AND IF (?4!='',extention = ?4,1=1) AND IF (?5!='',batid = ?5,1=1)",nativeQuery = true)
	List<Object> aggrCallcenterAiCollect(Date begin, Date end,String orgi,String extention,String batid);
	
	@Query("select count(case when dataid is not null and calltype='outsideline' and direction='out' THEN '1' end) as calltotal,"//0通话总量
			+"count(case when calltype='outsideline' and direction='out' and agent is not null and duration > 0 THEN '2' end) as answertotal,"//1接通总量
			+"count(case when calltype='outsidetrans' and direction='in' THEN '3' end) as aitranstotal,"//3转人工总量
			+"ROUND(sum(case when calltype='outsidetrans' and direction='in' THEN duration end)/1000) as sumAgentdurationMonitor, "//11转人工通话总时长
			+"ROUND(sum(case when calltype='outsidetrans' and direction='in' and waittime > 0 THEN waittime end)/1000) as sumWaittimeMonitor, "//12总等待时长
			+"count(DISTINCT case when calltype='outsidetrans' and direction='in' THEN username end) as countAgentMonitor  "//15坐席数量
		    +"FROM StatusEvent "
		    +"where forecast=1 and createtime >=?1 and createtime <=?2 and orgi=?3")
	Map<String,Object> aggrCallcenterForecastMonitor( Date begin, Date end,String orgi);
	
	@Query("select count(case when dataid is not null and calltype='outsideline' and direction='out' THEN '1' end) as calltotal,"//0通话总量
			+"count(case when calltype='outsideline' and direction='out' and agent is not null and duration > 0 THEN '2' end) as answertotal,"//1接通总量
			+"count(case when calltype='outsidetrans' and direction='in' THEN '3' end) as aitranstotal,"//3转人工总量
			+"ROUND(sum(case when calltype='outsidetrans' and direction='in' THEN duration end)/1000) as sumAgentdurationMonitor, "//11转人工通话总时长
			+"ROUND(sum(case when calltype='outsidetrans' and direction='in' and waittime > 0 THEN waittime end)/1000) as sumWaittimeMonitor, "//12总等待时长
			+"count(DISTINCT case when calltype='outsidetrans' and direction='in' THEN username end) as countAgentMonitor  "//15坐席数量
		    +"FROM StatusEvent "
		    +"where forecast=1 and createtime >=?1 and createtime <=?2 and orgi=?3 and actid=?4")
	Map<String,Object> aggrCallcenterForecastMonitor( Date begin, Date end,String orgi,String actid);
	
	@Query(value="select username,"//0
			+ "count(1) ,"//1通话总量
			+"ROUND(avg(recordtime/1000),2), "//2人工坐席平均通话时长----单位：秒 已更改为：单通平均通话时长
			+ "sum(duration),"//3人工坐席通话总时长---单位：毫秒
			+ "ROUND(avg(duration/1000),2) "//4人工坐席通话平均时长(秒)
		    +"FROM uk_callcenter_event "
		    +"where createtime >=?1 and createtime <=?2 and orgi=?3 and username is not null and forecast=1 and calltype='outsidetrans' and direction='in' AND IF(?4!='',actid = ?4,1=1) GROUP BY username",nativeQuery = true)
	List<Object> aggrForecastGroupbyUsername( Date begin, Date end,String orgi,String actid);
	
	@Query(value="select agent,"//0
			+ "count(*) ,"//1机器人外呼总量
			+ "ROUND(avg(case when duration > 0 THEN duration/1000 end),2),"//2机器人平均通话时长
			+ "ROUND(avg(ringduration/1000),2), "//3振铃平均时长
			+ "sum(duration),"//4机器人通话总时长
			+ "sum(asrtimes),"//5调用asr总次数
			+ "ROUND(avg(case when duration > 0 THEN asrtimes end),2), "//6调用asr平均次数
			+ "count(case when duration > 0 THEN '2' end) as aicutin "//7接通量
		    +"FROM uk_callcenter_event "
		    +"where createtime >=?1 and createtime <=?2 and orgi=?3 and skill is not null and forecast=1 and calltype='outsideline' and direction='out' AND IF (?4!='',actid = ?4,1=1) GROUP BY skill",nativeQuery = true)
	List<Object> aggrForecastGroupbySkill( Date begin, Date end,String orgi,String actid);
	
	//我的通话-统计指标
	@Query("select "//
			+"sum(case when calltype!='orgcallout' THEN 1 else 0 end) as incall ,"//1呼入电话
			+"sum(case when calltype='orgcallout' THEN 1 else 0 end) as outcall ,"//2呼出电话
			+"sum(case when duration=0 and misscall='1' THEN 1 else 0 end) as misscall ,"//3漏话总量
			+"count(id) as totalcall, "//4总通话量
			+"sum(case when duration>0 THEN duration else 0 end) as totalduration ,"//5总通话时长
			+"sum(case when calltype!='orgcallout' and duration>0 THEN duration else 0 end) as incallduration ,"//6呼入总通话时长
			+"sum(case when calltype='orgcallout' and duration>0 THEN duration else 0 end) as outcallduration, "//7呼出总通话时长
			+"sum(case when ringduration>0 THEN ringduration else 0 end) as ringduration ,"//8振铃总时长
			+"sum(case when calltype!='orgcallout' and duration=0 and misscall='1' THEN 1 else 0 end) as inmisscall ,"//9呼入漏话量
			+"sum(case when calltype='orgcallout' and duration=0 and misscall='1' THEN 1 else 0 end) as outmisscall ,"//10呼出漏话量
			+"sum(case when calltype!='orgcallout' and ringduration>0 THEN ringduration else 0 end) as inringduration ,"//11呼入振铃时长
			+"sum(case when calltype='orgcallout' and ringduration>0 THEN ringduration else 0 end) as outringduration ,"//12呼出振铃时长
			+"sum(case when ringduration>0 and duration=0 and misscall='1' THEN ringduration else 0 end) as misscallringduration ,"//13漏话振铃总时长
			+"sum(case when calltype!='orgcallout' and duration=0 and misscall='1' and ringduration>0 THEN ringduration else 0 end) as inmisscallringduration ,"//14呼入漏话振铃时长
			+"sum(case when calltype='orgcallout' and duration=0 and misscall='1' and ringduration>0 THEN ringduration else 0 end) as outmisscallringduration "//15呼出漏话振铃时长
		    +"FROM StatusEvent "
		    +"where orgi=?1 and userid=?2 and createtime >= ?3 and createtime <= ?4")
	Map<String,Long> aggrStatusEventByUserid(String orgi,String userid,Date begin,Date end);
	
}
